import Database from 'better-sqlite3';
import {QueryBuilder} from './QueryBuilder';
import {
  EntityClass,
  FieldFillType,
  LogicalDeleteConfig,
  LogicalDeleteInfo,
  PartialWithType,
  QueryOptions,
  TableInfo
} from "../types";
import {BaseEntity} from "./BaseEntity";
import {
  EntityDefaultValueUtils,
  EntityTypeProcessor,
  QueryBuilderUtils,
  SqlBuilderUtils,
  TransactionUtils
} from "../common";
import {DEFAULT_LOGICAL_DELETED_VALUE, DEFAULT_LOGICAL_NOT_DELETED_VALUE} from './DatabaseManager';

/**
 * 提供实体CRUD操作的基础服务类
 */
export class BaseService<T extends BaseEntity> {
  protected db: Database.Database;
  protected entityClass: EntityClass;
  protected tableInfo: TableInfo<T>;
  protected logicalDeleteConfig: LogicalDeleteConfig | null = null;
  protected logicalDeleteField: string | null = null;
  protected logicalDeleteInfo: LogicalDeleteInfo | null = null;

  /**
   * 为指定实体创建新服务
   * @param db 数据库实例
   * @param entityClass 实体类
   */
  constructor(db?: Database.Database, entityClass?: EntityClass) {
    if (db && entityClass) {
      this.db = db;
      this.entityClass = entityClass;
      this.tableInfo = entityClass.getTableInfo();

      // 初始化逻辑删除配置
      this.initLogicalDeleteConfig();
    } else {
      throw new Error('数据库实例和实体类必须提供');
    }
  }

  /**
   * 初始化逻辑删除配置
   * 按优先级从高到低检查：
   * 1. 实体字段装饰器配置
   * 2. 数据库全局配置
   */
  private initLogicalDeleteConfig(): void {
    // 检查数据库是否有全局逻辑删除配置
    if (this.tableInfo.db.logicalDeleteConfig) {
      this.logicalDeleteConfig = this.tableInfo.db.logicalDeleteConfig;
      this.logicalDeleteField = this.logicalDeleteConfig.deletedField || 'deleted';
    }

    // 检查实体是否有使用@LogicalDelete装饰器的字段
    for (const [fieldName, column] of Object.entries<any>(this.tableInfo.columns)) {
      if (column.logicalDelete) {
        this.logicalDeleteField = fieldName;
        this.logicalDeleteInfo = column.logicalDelete;
        break;
      }
    }
  }

  /**
   * 获取逻辑删除字段的值
   * @param isDeleted 是否是已删除状态
   * @returns 对应状态的字段值
   */
  private getLogicalDeleteValue(isDeleted: boolean): any {
    // 使用DatabaseManager中定义的默认值
    const defaultDeletedValue = DEFAULT_LOGICAL_DELETED_VALUE;
    const defaultNotDeletedValue = DEFAULT_LOGICAL_NOT_DELETED_VALUE;

    if (this.logicalDeleteInfo) {
      // 优先使用字段级配置
      // 确保有值，即使字段上的LogicalDelete装饰器没有提供参数
      return isDeleted
        ? (this.logicalDeleteInfo.deletedValue !== undefined ? this.logicalDeleteInfo.deletedValue : defaultDeletedValue)
        : (this.logicalDeleteInfo.notDeletedValue !== undefined ? this.logicalDeleteInfo.notDeletedValue : defaultNotDeletedValue);
    } else if (this.logicalDeleteConfig) {
      // 其次使用全局配置
      return isDeleted
        ? (this.logicalDeleteConfig.deletedValue !== undefined ? this.logicalDeleteConfig.deletedValue : defaultDeletedValue)
        : (this.logicalDeleteConfig.notDeletedValue !== undefined ? this.logicalDeleteConfig.notDeletedValue : defaultNotDeletedValue);
    }
    // 默认返回数值
    return isDeleted ? defaultDeletedValue : defaultNotDeletedValue;
  }

  /**
   * 检查是否启用了逻辑删除
   * @returns 是否启用了逻辑删除
   */
  private isLogicalDeleteEnabled(): boolean {
    return !!this.logicalDeleteField;
  }

  /**
   * 添加逻辑删除条件到查询选项
   * @param options 查询选项
   * @returns 添加了逻辑删除条件的查询选项
   */
  private addLogicalDeleteCondition(options: QueryOptions<T> = {}): QueryOptions<T> {
    if (!this.isLogicalDeleteEnabled() || !this.logicalDeleteField) {
      return options;
    }

    const notDeletedValue = this.getLogicalDeleteValue(false);
    const newOptions = {...options};

    if (!newOptions.where) {
      newOptions.where = {};
    }

    // 如果未指定该字段的条件，添加未删除条件
    const fieldName = this.logicalDeleteField as keyof T & string;
    if (newOptions.where[fieldName] === undefined) {
      newOptions.where[fieldName] = notDeletedValue;
    }

    return newOptions;
  }

  /**
   * 向数据库插入新实体
   * @param entity 要插入的实体
   * @returns 插入实体的ID
   */
  insert(entity: Partial<T>): number {
    // 使用工具类为实体应用默认值
    const completeEntity = EntityDefaultValueUtils.applyDefaultValues(entity, this.tableInfo.columns);

    // 处理自动ID生成
    this.entityClass.processAutoId(completeEntity as any);

    // 处理自动填充
    this.entityClass.processAutoFill(completeEntity as any, FieldFillType.INSERT);

    // 确保逻辑删除字段为未删除状态
    if (this.isLogicalDeleteEnabled() && this.logicalDeleteField) {
      const fieldName = this.logicalDeleteField as keyof typeof completeEntity;
      if (completeEntity[fieldName] === undefined) {
        completeEntity[fieldName] = this.getLogicalDeleteValue(false);
      }
    }

    // 获取数据库管理器的类型校验配置
    const strictTypeCheck = this.tableInfo.db.isStrictTypeCheckEnabled();

    // 使用EntityTypeProcessor处理实体数据
    const {keys, values} = EntityTypeProcessor.processEntityData(
      completeEntity,
      this.tableInfo,
      strictTypeCheck
    );

    // 使用SqlBuilderUtils构建并执行INSERT语句
    const sql = SqlBuilderUtils.buildInsertSql(this.tableInfo.name, keys);
    const stmt = this.db.prepare(sql);
    const result = stmt.run(...values);

    return result.lastInsertRowid as number;
  }

  /**
   * 在单个事务中插入多个实体
   * @param entities 要插入的实体数组
   */
  batchInsert(entities: Partial<T>[]): void {
    if (entities.length === 0) return;

    TransactionUtils.executeTransaction(this.db, () => {
      for (const item of entities) {
        this.insert(item);
      }
    });
  }

  /**
   * 通过ID检索实体
   * @param id 实体ID
   * @returns 实体或null（如果未找到）
   */
  getById(id: number | string): T | null {
    const primaryKey = this.tableInfo.primaryKey || 'id';

    // 构建基本查询
    let sql = `SELECT * FROM ${this.tableInfo.name} WHERE ${primaryKey} = ?`;
    const params: any[] = [id];

    // 添加逻辑删除条件
    if (this.isLogicalDeleteEnabled() && this.logicalDeleteField) {
      sql += ` AND ${this.logicalDeleteField} = ?`;
      params.push(this.getLogicalDeleteValue(false));
    }

    const stmt = this.db.prepare(sql);
    const result = stmt.get(...params);

    return result as T || null;
  }

  /**
   * 查找匹配指定条件的实体
   * @param options 查询选项
   * @returns 匹配实体的数组
   */
  find(options: QueryOptions<T> = {}): T[] {
    // 添加逻辑删除条件
    const queryOptions = this.addLogicalDeleteCondition(options);

    const params: any[] = [];
    let whereClause: string | undefined;

    // 处理WHERE条件
    if (queryOptions.where && Object.keys(queryOptions.where).length > 0) {
      const {clause, params: whereParams} = QueryBuilderUtils.buildWhereClause(queryOptions.where);
      whereClause = clause;
      params.push(...whereParams);
    }

    // 处理ORDER BY
    const orderByClause = QueryBuilderUtils.buildOrderByClause(queryOptions.orderBy);

    // 处理分页
    const {
      clause: paginationClause,
      params: paginationParams
    } = QueryBuilderUtils.buildPaginationClause(queryOptions.limit, queryOptions.offset);

    if (paginationParams.length > 0) {
      params.push(...paginationParams);
    }

    // 使用SqlBuilderUtils构建SELECT语句
    const sql = SqlBuilderUtils.buildSelectSql(
      this.tableInfo.name,
      ['*'],
      whereClause,
      orderByClause,
      paginationClause
    );

    const stmt = this.db.prepare(sql);
    const results = stmt.all(...params);

    return results as T[];
  }

  /**
   * 查找匹配指定条件的单个实体
   * @param options 查询选项
   * @returns 匹配的实体或null（如果未找到）
   */
  findOne(options: QueryOptions<T> = {}): T | null {
    options.limit = 1;
    const results = this.find(options);
    return results.length > 0 ? results[0] : null;
  }

  /**
   * 计算匹配指定条件的实体数量
   * @param options 查询选项
   * @returns 匹配实体的数量
   */
  count(options: QueryOptions<T> = {}): number {
    // 添加逻辑删除条件
    const queryOptions = this.addLogicalDeleteCondition(options);

    const params: any[] = [];
    let whereClause: string | undefined;

    // 处理WHERE条件
    if (queryOptions.where && Object.keys(queryOptions.where).length > 0) {
      const {clause, params: whereParams} = QueryBuilderUtils.buildWhereClause(queryOptions.where);
      whereClause = clause;
      params.push(...whereParams);
    }

    // 使用SqlBuilderUtils构建COUNT SQL语句
    const sql = SqlBuilderUtils.buildCountSql(this.tableInfo.name, whereClause);

    const stmt = this.db.prepare(sql);
    const result = stmt.get(...params) as { count: number } | undefined;

    return result?.count || 0;
  }

  /**
   * 更新匹配条件的实体
   * @param entity 要更新的实体字段
   * @param whereCondition WHERE条件（如果未提供，则使用主键）
   * @returns 受影响的行数
   */
  update(entity: Partial<T>, whereCondition?: PartialWithType<T>): number {
    const primaryKey = this.tableInfo.primaryKey || 'id';
    const updateData = {...entity};

    // Handle case where primary key is in entity but no where condition is provided
    if (primaryKey in updateData && !whereCondition) {
      const id = updateData[primaryKey as keyof typeof updateData];
      delete updateData[primaryKey as keyof typeof updateData];
      whereCondition = {[primaryKey]: id} as PartialWithType<T>;
    }

    if (!whereCondition || Object.keys(whereCondition).length === 0) {
      throw new Error('Update operation requires a condition');
    }

    // 添加逻辑删除条件，防止更新已删除的记录
    if (this.isLogicalDeleteEnabled() && this.logicalDeleteField) {
      const fieldName = this.logicalDeleteField as keyof T & string;
      if (whereCondition[fieldName] === undefined) {
        whereCondition = {
          ...whereCondition,
          [fieldName]: this.getLogicalDeleteValue(false)
        } as PartialWithType<T>;
      }
    }

    // 处理自动填充
    this.entityClass.processAutoFill(updateData as any, FieldFillType.UPDATE);

    // 获取数据库管理器的类型校验配置
    const strictTypeCheck = this.tableInfo.db.isStrictTypeCheckEnabled();

    // 使用EntityTypeProcessor处理更新数据
    const {keys, values} = EntityTypeProcessor.processEntityData(
      updateData,
      this.tableInfo,
      strictTypeCheck
    );

    if (keys.length === 0) {
      return 0;
    }

    // 使用EntityTypeProcessor处理WHERE条件
    const processedWhereCondition = EntityTypeProcessor.processWhereCondition(
      whereCondition,
      this.tableInfo
    );

    // 准备WHERE条件
    const wherePairs = Object.entries(processedWhereCondition);
    const whereValues = wherePairs.map(p => p[1]);

    // 使用SqlBuilderUtils构建并执行UPDATE语句
    const sql = SqlBuilderUtils.buildUpdateSql(
      this.tableInfo.name,
      keys,
      wherePairs
    );

    const stmt = this.db.prepare(sql);
    const result = stmt.run(...values, ...whereValues);

    return result.changes;
  }

  /**
   * 删除匹配条件的实体（逻辑删除或物理删除）
   * @param whereCondition WHERE条件
   * @returns 受影响的行数
   */
  delete(whereCondition: PartialWithType<T>): number {
    const originalWherePairs = Object.entries(whereCondition);

    if (originalWherePairs.length === 0) {
      throw new Error('Delete operation requires a condition');
    }

    // 如果启用了逻辑删除，执行更新操作而不是删除
    if (this.isLogicalDeleteEnabled() && this.logicalDeleteField) {
      const updateData = {
        [this.logicalDeleteField]: this.getLogicalDeleteValue(true)
      } as unknown as Partial<T>;

      // 添加条件，只更新未删除的记录
      const newCondition = {...whereCondition} as PartialWithType<T>;
      const fieldName = this.logicalDeleteField as keyof T & string;

      // 如果未指定该字段的条件，只操作未删除的记录
      if (newCondition[fieldName] === undefined) {
        newCondition[fieldName] = this.getLogicalDeleteValue(false);
      }

      return this.update(updateData, newCondition);
    }

    // 物理删除
    // 获取数据库管理器的类型校验配置
    const strictTypeCheck = this.tableInfo.db.isStrictTypeCheckEnabled();

    // 使用EntityTypeProcessor处理WHERE条件
    const processedWhereCondition = EntityTypeProcessor.processWhereCondition(
      whereCondition,
      this.tableInfo,
      strictTypeCheck
    );

    // 准备WHERE条件
    const processsedWherePairs = Object.entries(processedWhereCondition);
    const whereValues = processsedWherePairs.map(p => p[1]);

    // 使用SqlBuilderUtils构建DELETE语句
    const sql = SqlBuilderUtils.buildDeleteSql(this.tableInfo.name, processsedWherePairs);

    const stmt = this.db.prepare(sql);
    const result = stmt.run(...whereValues);

    return result.changes;
  }

  /**
   * 通过ID删除实体
   * @param id 实体ID
   * @returns 受影响的行数（1表示成功，0表示未找到）
   */
  deleteById(id: number | string): number {
    const primaryKey = this.tableInfo.primaryKey || 'id';
    return this.delete({[primaryKey]: id} as PartialWithType<T>);
  }

  /**
   * 物理删除实体（即使启用了逻辑删除）
   * @param whereCondition WHERE条件
   * @returns 受影响的行数
   */
  forceDelete(whereCondition: PartialWithType<T>): number {
    const originalWherePairs = Object.entries(whereCondition);

    if (originalWherePairs.length === 0) {
      throw new Error('Delete operation requires a condition');
    }

    // 获取数据库管理器的类型校验配置
    const strictTypeCheck = this.tableInfo.db.isStrictTypeCheckEnabled();

    // 使用EntityTypeProcessor处理WHERE条件
    const processedWhereCondition = EntityTypeProcessor.processWhereCondition(
      whereCondition,
      this.tableInfo,
      strictTypeCheck
    );

    // 准备WHERE条件
    const processsedWherePairs = Object.entries(processedWhereCondition);
    const whereValues = processsedWherePairs.map(p => p[1]);

    // 使用SqlBuilderUtils构建DELETE语句
    const sql = SqlBuilderUtils.buildDeleteSql(this.tableInfo.name, processsedWherePairs);

    const stmt = this.db.prepare(sql);
    const result = stmt.run(...whereValues);

    return result.changes;
  }

  /**
   * 通过ID物理删除实体
   * @param id 实体ID
   * @returns 受影响的行数
   */
  forceDeleteById(id: number | string): number {
    const primaryKey = this.tableInfo.primaryKey || 'id';
    return this.forceDelete({[primaryKey]: id} as PartialWithType<T>);
  }

  /**
   * 查找包括已删除记录在内的所有记录
   * @param options 查询选项
   * @returns 匹配实体的数组（包括已删除记录）
   */
  findWithDeleted(options: QueryOptions<T> = {}): T[] {
    // 不添加逻辑删除条件，直接使用原始查询
    return this.find(options);
  }

  /**
   * 恢复已删除的记录
   * @param whereCondition WHERE条件
   * @returns 受影响的行数
   */
  restore(whereCondition: PartialWithType<T>): number {
    if (!this.isLogicalDeleteEnabled() || !this.logicalDeleteField) {
      return 0; // 未启用逻辑删除，无需恢复
    }

    const updateData = {
      [this.logicalDeleteField]: this.getLogicalDeleteValue(false)
    } as unknown as Partial<T>;

    // 添加条件，只更新已删除的记录
    const newCondition = {...whereCondition} as PartialWithType<T>;
    const fieldName = this.logicalDeleteField as keyof T & string;

    // 如果未指定该字段的条件，只操作已删除的记录
    if (newCondition[fieldName] === undefined) {
      newCondition[fieldName] = this.getLogicalDeleteValue(true);
    }

    return this.update(updateData, newCondition);
  }

  /**
   * 通过ID恢复已删除的记录
   * @param id 实体ID
   * @returns 受影响的行数
   */
  restoreById(id: number | string): number {
    const primaryKey = this.tableInfo.primaryKey || 'id';
    return this.restore({[primaryKey]: id} as PartialWithType<T>);
  }

  /**
   * 执行自定义查询并返回结果
   * @param sql SQL查询
   * @param params 查询参数
   * @returns 查询结果
   */
  query<R = any>(sql: string, params: any[] = []): R[] {
    const stmt = this.db.prepare(sql);
    return stmt.all(...params) as R[];
  }

  /**
   * 执行自定义SQL语句
   * @param sql SQL语句
   * @param params 语句参数
   * @returns 执行结果
   */
  execute(sql: string, params: any[] = []): Database.RunResult {
    const stmt = this.db.prepare(sql);
    return stmt.run(...params);
  }

  /**
   * 创建一个类型安全的QueryBuilder实例
   * 允许构建更复杂的查询
   * @returns QueryBuilder实例
   */
  createQueryBuilder(): QueryBuilder<T> {
    return new QueryBuilder<T>(this.db, this.entityClass);
  }
}
